﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
using System.Data.SqlClient;
using System.Data;
using MySql.Data.MySqlClient;

namespace AsianSocietyPictureUpload
{
    public partial class AsianSocietyUploadPicturesToDatabase
    {
        private void CheckNewUpload()
        {
            if (System.IO.Directory.Exists(newuploadPath))
            {
                string[] filePaths = Directory.GetFiles(newuploadPath);
                for (int i = 0; i < filePaths.Length; i++)
                {
                    try
                    {
                        file = filePaths[i].ToString();
                        fileName = System.IO.Path.GetFileName(filePaths[i].ToString());
                        string Destination; 

                        
                        int mid;

                        mid = FindMainPolutionImgID(fileName);

                        if (mid > 0) //Main Image
                        {
                            ProcessRawGround(file, fileName);
                            QueryExecution("update chinaair.tb_city_pollution set mainimg1 = 'display/ground/" + fileName + "' where mid = " + mid);
                        }
                        else
                        {
                            mid = FindSatePolutionImageOneID(fileName);

                            if (mid > 0) //Sata Image 1
                            {
                                int cityID = FindCityID(mid);
                                ProcessRawSataImg(file, fileName, cityID);
                                QueryExecution("update chinaair.tb_city_pollution set comimg1 = 'display/sate/" + fileName + "' where mid = " + mid);
                            }
                            else
                            {
                                mid = FindSatePolutionImageTwoID(fileName);

                                if (mid > 0) //SataImage 2
                                {
                                    int cityID = FindCityID(mid);
                                    ProcessRawSataImg(file, fileName, cityID);
                                    QueryExecution("update chinaair.tb_city_pollution set comimg2 = 'display/sate/" + fileName + "' where mid = " + mid);
                                }
                                else
                                {
                                    mid = FindSponserID(fileName);

                                    if (mid > 0) //Sponser
                                    {
                                        Destination = sponserPath + fileName;
                                        System.IO.File.Move(file, Destination);

                                        QueryExecution("Update chinaair.tb_sponsor_tb set zimg = 'sponser/" + fileName + "' where mid = " + mid);
                                    }
                                    else
                                    {
                                        mid = FindcityMapID(fileName);
                                        if (mid > 0) //CityMap
                                        {


                                            Destination = cityMapPath + fileName;
                                            System.IO.File.Move(file, Destination);

                                            QueryExecution("Update chinaair.tb_city_tb set zmap ='" + "map/" + fileName + "' where mid = " + mid);
                                        }
                                    }
                                }
                            }
                        }

                    }
                    catch (Exception e) { string error = e.Message; }
                }
            }
        }

        private void ProcessRawGround(string file, string fileName)
        {
            Image rawImage;

            rawImage = CropImageFromRawGround(file);
            rawImage = ResizeImage(rawImage);
            rawImage = SaveImageToLocalFolderGroundFromRaw(rawImage, fileName);
            rawImage.Dispose();
            rawImage = null;

            File.Delete(file);
        }

        private void ProcessRawSataImg(string file, string fileName, int cityID)
        {
            Image rawImage;

            rawImage = CropImageFromRawSatellite(file);
            rawImage = ResizeImage(rawImage);
            rawImage = TextOverLayImageForRawSatellite(cityID, rawImage);
            rawImage = SaveImageToLocalFolderSatelliteFromRaw(rawImage, fileName);
            rawImage.Dispose();
            rawImage = null;
            File.Delete(file);
        }

        private void QueryExecution(string sqlcommand)
        {
            MySql.Data.MySqlClient.MySqlCommand updateMainImage = new MySql.Data.MySqlClient.MySqlCommand(sqlcommand);
            MySqlConnection mySQLCon = new MySqlConnection(setString.chinaairConnectionString);

            mySQLCon.Open();

            updateMainImage.Connection = mySQLCon;
            updateMainImage.ExecuteNonQuery();

            mySQLCon.Close();
        }

        private int MidReader(string sqlCommand)
        {
            MySqlConnection mySQLCon = new MySqlConnection(setString.chinaairConnectionString);

            mySQLCon.Open();

            MySqlCommand checkMID = new MySql.Data.MySqlClient.MySqlCommand(sqlCommand, mySQLCon);

            MySqlDataReader dr = checkMID.ExecuteReader();


            int MID = 0;


            while (dr.Read())
            {
                MID = Convert.ToInt32(dr[0]);
            }

            dr.Close();
            mySQLCon.Close();

            return MID;
        }

        private int FindMainPolutionImgID(string fileName)
        {
            string sqlCommand = "Select mid from chinaair.tb_city_pollution where mainimg1 = '" + fileName + "'";

            return MidReader(sqlCommand);
        }

        private int FindSatePolutionImageOneID(string fileName)
        {
            string sqlCommand = "select mid from chinaair.tb_city_pollution where comimg1 = '" + fileName + "'";

            return MidReader(sqlCommand);
        }

        private int FindCityID(int mid)
        {
            string sqlCommand = "select fcityid from chinaair.tb_city_pollution where mid =" + mid;

            return MidReader(sqlCommand);
        }

        private int FindSatePolutionImageTwoID(string fileName)
        {
            string sqlCommand = "select mid from chinaair.tb_city_pollution where comimg2 ='" + fileName + "'";

            return MidReader(sqlCommand);
        }

        private int FindSponserID(string fileName)
        {
            string sqlComand = "SELECT mid FROM chinaair.tb_sponsor_tb where zimg='" + fileName + "'";

            return MidReader(sqlComand);
        }

        private int FindcityMapID(string fileName)
        {
            string sqlComand = "SELECT * FROM chinaair.tb_city_tb where zmap ='" + fileName + "'";

            return MidReader(sqlComand);
        }
    }
}
